using System;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;

namespace uk.co.tonyditchfield.tort.database.language
{
    /// <summary>
    /// SQL Server syntax engine
    /// </summary>
    internal class SQLDBSyntax : DBSyntax, IDBSyntax
    {
        /// <summary>
        /// SQL Server Database engine syntax generator
        /// </summary>
        /// <param name="obj">The object to persist</param>
        /// <param name="command">The SQLCommand object to pass parameters and statements to.</param>
        internal SQLDBSyntax(Object obj, IDbCommand command) 
            : base(obj, command) { }

        #region IDBSyntax Members
        /// <summary>
        /// Constructs an SQL Server based insert statement 
        /// and binds it to the command passed in the constructor
        /// </summary>
        int IDBSyntax.BuildInsert()
        {
            int i = 1;
            beginInsert.Append("INSERT INTO ");
            beginInsert.Append(mapping.TableName + " (");

            foreach (string column in mapping.PersistableMapping.Keys)
            {
                ParameterName = "@" + column;
                if (i == mapping.PersistableMapping.Count)
                {
                    beginInsert.Append(column + ") VALUES (");
                    endInsert.Append(ParameterName + ")");
                }
                else
                {
                    beginInsert.Append(column + ", ");
                    endInsert.Append(ParameterName + ", ");
                }
                i++;
            }
            beginInsert.Append(endInsert);

            translation.PrimeKeyColumn primeKeyColumn = null;

            foreach (translation.DBColumn column in mapping.DbObject.DbColumns)
            {
                if (column.GetType().Equals(typeof(translation.PrimeKeyColumn)) && 
                    ((translation.PrimeKeyColumn)column).OutputRequired == true)
                {
                    primeKeyColumn = (translation.PrimeKeyColumn)column;
                    beginInsert.Append(" SET " + primeKeyColumn.ParameterName + "= SCOPE_IDENTITY()");
                }
            }
            logger.LogInfo("SQL String: " + beginInsert.ToString());
            Command.CommandText = beginInsert.ToString();
            try
            {
                int rowsAffected = Command.ExecuteNonQuery();
                PrimeKey = ((SqlParameter)Command.Parameters[primeKeyColumn.ParameterName]).Value;
                
                return rowsAffected;
            }
            catch (InvalidOperationException ie)
            {
                throw new exception.PersistenceException(ie.Message, ie);
            }
            catch (SqlException se)
            {
                throw new exception.PersistenceException(se.Message, se);
            }
            catch (Exception e)
            {
                throw new exception.PersistenceException(e.Message, e);
            }
        }

        int IDBSyntax.BuildDelete()
        {
            throw new Exception("The method or operation is not implemented.");
        }

        IDataReader IDBSyntax.BuildSelect()
        {
            throw new Exception("The method or operation is not implemented.");
        }

        int IDBSyntax.BuildUpdate()
        {
            throw new Exception("The method or operation is not implemented.");
        }

        #endregion
    }
}
